Add grand totals and sub-totals to your grids to provide additional context and information. Totals can be enabled either through the right-click context menus or via the Totals dialog box which offers more options for setting and configuring the totaling logic deployed. The dialog is explained below.
Note: Sub-totals cannot function together with sorting, filtering, N-of-N and elimination logic.
- Click here to learn how to disable total formatting.
Using the Totals Dialog
Quick totals are normally enabled via the context menu and apply only to grid visualizations. The alternative technique is to turn on totals using the ribbon buttons and the associated dialogs. This offers finer control over both the total logic.
- Toggle the Totals button from the Home ribbon to show or hide totals
- By default, grand totals will be added to the grid
- There are a few ways to remove totals from the grid:
- You can toggle the Totals button off to remove all totals
- Click the drop-down and select Remove Totals
- Remove totals for columns or rows only - click the drop down, and under columns or rows select Remove Totals
- Select Remove Totals from the Totals dialog
- Open the Totals dialog box to customize the way totals are shown
- There is a separate dialog for columns and rows - click the Totals drop down, and under Columns or Rows, select Totals to open the dialog
Total Position
Bottom Totals: adds an additional row or column at the end or bottom of the grid - this is the default position.
Top Totals: adds an additional row or column at the start or top of the grid.
Totals Type
Grand Totals Only: displays grand totals only:
Grand and Sub-Totals: displays both grand totals and sub-totals:
Sub Totals: displays sub-totals only:
Total Functions
- Aggregate (default): uses the aggregation type defined within the source database.
- Sum: displays the sum of the totaled cells.
- Count: displays the number of contributing cells. Empty cells are NOT counted.
- Average: displays the average value of the contributing cells. Empty cells are NOT included in calculation.
- Minimum: displays the minimum value from among the contributing cells.
- Maximum: displays the maximum value from among the contributing cells
- Median: displays the median value of the contributing cells. Empty cells are NOT included in calculation.
- Standard Deviation: displays the standard deviation between the contributing cells. Empty are NOT included in calculation.
Advanced Formulation Options for Totals
Advanced formulation options are available on MDX based data sources only.
Logic Options
- Bottom-Up "Hierarchical" : When users total multi-level or 'regular' hierarchies, the engine will add up all the leaf level items from the bottom, ignoring any parent elements in the tree. This is the default "hierarchical" logic option. In some situations, this approach might not be appropriate or the desired effect.
- Top-Down Hierarchical: This will add all the nodes in a hierarchy starting from the top of the hierarchy working its way down, ignoring any child elements of existing parent objects in the tree.
- Flat: will add up all visible elements int eh tree, regardless of any ancestor or descendant objects.
Formulation Options
- Ignore Model Scoping: this informs the query engine to ignore the 'scope isolation' switch in Microsoft OLAP. This changes the behavior of client side calculations vs server side calculations.
- Include Calculated Member: this informs to include calculated members in totals.
- Overwrite Solve Order: in situations where there are overlapping calculations, the order in which they are solved can impact the totals. This allows you to specify the solve order for the totals themselves.
- Intelligent Totals: this switch allows the engine to heuristically decide on the fastest way to add totals to your query. The switch allows you to turn it off in the event the approach makes things slower in your specific circumstance.
- Force Data Source Totals: The totaling engine will, by default, automatically determine if it needs to calculate totals on the data source engine or if it can be resolved accurately by Pyramid (which is faster). Check this box to override this logic and force all totals to be calculated on the data source only.
- Optimize Totals: This matches the settings that can be triggered from the query settings menu.
- Context Heuristics: applies 'EXISTING' functional logic when the attributes used in one of the totals derive from the same dimension of a Microsoft OLAP or Tabular model. For more information on the EXISTING function, please see here.
- Calculate in Sub Query: splits ach of the totals combinations into multiple queries. Each query will then put the relevant members into the SUB QUERY of the MDX. This can sometimes greatly optimize the performance of the query. This does not work under the following circumstances:
When 'Include Calculated Member' is on.
When “Flat” is chosen from Logic Options.
If any hierarchies in the query are Non-Aggregatable (have no ALL member)